Amazon Redshift 分散スタイルのテーブル最適化を有効化するビューを作成してみた

--DROP VIEW admin.v_generate_alter_diststyle_backup_ddl;
/**********************************************************************************************
Purpose: View to retrieve the DDL for the diststyles of existing tables.

Notes:   Backup the diststyle settings of existing tables with diststyle configured.
         Generate SQL to restore tables with diststyles to their original diststyle settings later.

         The following filters are useful:
           where tablename in ('t1', 't2')     -- only get DDL for specific tables
           where schemaname in ('s1', 's2')    -- only get DDL for specific schemas

History:
2024-11-15 Satoru Ishikawa Created
**********************************************************************************************/
CREATE OR REPLACE VIEW v_generate_alter_diststyle_backup_ddl
AS
SELECT
 table_id
 ,schemaname
 ,tablename
 ,seq
 ,ddl
FROM
 (
 --DISTSTYLE
  SELECT
  c.oid::bigint as table_id
  ,n.nspname AS schemaname
  ,c.relname AS tablename
  ,300000001 AS seq
  ,CASE WHEN c.reldiststyle = 0 THEN 'ALTER TABLE ' || c.relname || ' ALTER DISTSTYLE EVEN;'
   WHEN c.reldiststyle = 1 THEN 'ALTER TABLE ' || c.relname || ' ALTER '
   WHEN c.reldiststyle = 8 THEN 'ALTER TABLE ' || c.relname || ' ALTER DISTSTYLE ALL;'
   WHEN c.reldiststyle = 9 THEN 'ALTER TABLE ' || c.relname || ' ALTER DISTSTYLE AUTO;'
   ELSE '<<Error - UNKNOWN DISTSTYLE>>'
   END AS ddl
 FROM pg_namespace AS n
 INNER JOIN pg_class AS c ON n.oid = c.relnamespace
 WHERE c.relkind = 'r'
 --DISTKEY COLUMNS
 UNION SELECT
  c.oid::bigint as table_id
  ,n.nspname AS schemaname
  ,c.relname AS tablename
  ,400000000 + a.attnum AS seq
  ,'DISTKEY (' + QUOTE_IDENT(a.attname) + ');' AS ddl
 FROM pg_namespace AS n
 INNER JOIN pg_class AS c ON n.oid = c.relnamespace
 INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
 WHERE c.relkind = 'r'
   AND c.reldiststyle <> 9
   AND a.attisdistkey IS TRUE
   AND a.attnum > 0
)
ORDER BY table_id,schemaname, tablename, seq
;

dev=# select * from v_generate_alter_diststyle_backup_ddl where schemaname = 'public';
 table_id | schemaname | tablename |    seq    |                    ddl                     
----------+------------+-----------+-----------+--------------------------------------------
   277586 | public     | orders    | 300000001 | ALTER TABLE orders ALTER DISTSTYLE AUTO;
   277588 | public     | returns   | 300000001 | ALTER TABLE returns ALTER DISTSTYLE AUTO;
   293976 | public     | category  | 300000001 | ALTER TABLE category ALTER DISTSTYLE AUTO;
   293979 | public     | date      | 300000001 | ALTER TABLE date ALTER DISTSTYLE AUTO;
   293982 | public     | event     | 300000001 | ALTER TABLE event ALTER DISTSTYLE AUTO;
   293985 | public     | listing   | 300000001 | ALTER TABLE listing ALTER DISTSTYLE AUTO;
   293988 | public     | sales     | 300000001 | ALTER TABLE sales ALTER DISTSTYLE AUTO;
   293991 | public     | users     | 300000001 | ALTER TABLE users ALTER DISTSTYLE AUTO;
   293994 | public     | venue     | 300000001 | ALTER TABLE venue ALTER DISTSTYLE AUTO;
   302174 | public     | part      | 300000001 | ALTER TABLE part ALTER DISTSTYLE AUTO;
   326738 | public     | testtab   | 300000001 | ALTER TABLE testtab ALTER 
   326738 | public     | testtab   | 400000001 | DISTKEY (catid);
   334930 | public     | testtab1  | 300000001 | ALTER TABLE testtab1 ALTER 
   334930 | public     | testtab1  | 400000001 | DISTKEY (catid);
(14 rows)

 /**********************************************************************************************
Purpose: View to retrieve the DDL for setting the diststyles of a table to "AUTO".

Notes:   Set the diststyle to "AUTO" for existing tables that already have diststyle configured. 
         It is recommended to backup the existing diststyle settings so that you can revert to 
         the original configuration later if needed.

         The following filters are useful:
           where tablename in ('t1', 't2')     -- only get DDL for specific tables
           where schemaname in ('s1', 's2')    -- only get DDL for specific schemas

History:
2024-11-15 Satoru Ishikawa Created
**********************************************************************************************/
CREATE OR REPLACE VIEW public.v_generate_alter_diststyle_auto_ddl
AS
 SELECT
  table_id
  ,schemaname
  ,tablename
  ,seq
  ,ddl
 FROM
  (
  --DISTSTYLE
   SELECT
   c.oid::bigint as table_id
   ,n.nspname AS schemaname
   ,c.relname AS tablename
   ,300000001 AS seq
   ,'ALTER TABLE ' || c.relname || ' ALTER DISTSTYLE AUTO;' AS ddl
  FROM pg_namespace AS n
  INNER JOIN pg_class AS c ON n.oid = c.relnamespace
  WHERE c.relkind = 'r'
)
ORDER BY table_id,schemaname, tablename, seq
;

dev=# select * from v_generate_alter_diststyle_auto_ddl where schemaname = 'public';
 table_id | schemaname | tablename |    seq    |                    ddl                     
----------+------------+-----------+-----------+--------------------------------------------
   277586 | public     | orders    | 300000001 | ALTER TABLE orders ALTER DISTSTYLE AUTO;
   277588 | public     | returns   | 300000001 | ALTER TABLE returns ALTER DISTSTYLE AUTO;
   293976 | public     | category  | 300000001 | ALTER TABLE category ALTER DISTSTYLE AUTO;
   293979 | public     | date      | 300000001 | ALTER TABLE date ALTER DISTSTYLE AUTO;
   293982 | public     | event     | 300000001 | ALTER TABLE event ALTER DISTSTYLE AUTO;
   293985 | public     | listing   | 300000001 | ALTER TABLE listing ALTER DISTSTYLE AUTO;
   293988 | public     | sales     | 300000001 | ALTER TABLE sales ALTER DISTSTYLE AUTO;
   293991 | public     | users     | 300000001 | ALTER TABLE users ALTER DISTSTYLE AUTO;
   293994 | public     | venue     | 300000001 | ALTER TABLE venue ALTER DISTSTYLE AUTO;
   302174 | public     | part      | 300000001 | ALTER TABLE part ALTER DISTSTYLE AUTO;
   326738 | public     | testtab   | 300000001 | ALTER TABLE testtab ALTER DISTSTYLE AUTO;
   334930 | public     | testtab1  | 300000001 | ALTER TABLE testtab1 ALTER DISTSTYLE AUTO;
(12 rows)